






use ${output}cbp_innovindex.dta, clear

egen avgpuse1980 = mean(puse1980)  if year==1977, by(numsic3 year)
egen avgzpuse1980 = mean(zpuse1980)  if year==1977, by(numsic3 year)
egen avgcuse1980 = mean(cuse1980)  if year==1977, by(numsic3 year)
egen avgzcuse1980 = mean(zcuse1980)  if year==1977, by(numsic3 year)
foreach var in avgpuse1980 avgzpuse1980 avgcuse1980 avgzcuse1980 {
   egen temp = max(`var'), by(numsic3)
   replace `var' = temp if `var'==.
   drop temp
}

foreach yy in 77 97 {
   gen emp`yy' = imputed_emp if year==19`yy'
   egen temp = max(emp`yy'), by(numfips numsic3)
   replace emp`yy' = temp if emp`yy'==.
   drop temp

}

foreach yy in  97 {
   gen demp77`yy' = emp`yy' - emp77
   gen gremp77`yy' = demp77`yy'/emp77
}

foreach var in pmfg1980 puse1980 cuse1980 {
   foreach yy in 77 92 { 
      egen county`var'`yy' = sum(`var') if year==19`yy', by(numfips year)
      gen shr`var'`yy' = `var'/county`var'`yy'
      egen temp = max(shr`var'`yy'), by(numfips numsic3)
      replace shr`var'`yy' = temp if shr`var'`yy' == .
      drop temp county`var'`yy'	  
   } 
}


keep if year==1977
count
keep fips numfips state county year sic3 numsic3 name_sic3 sectorname emp *emp*  qp1 ap univsur univcont pmfg1980 puse1980 cuse1980 zpmfg1980 zpuse1980 zcuse1980  log*emp*  d*emp* gr*emp* shr* 
save ${inter}visvar1977.dta, replace




foreach var in zcuse1980 demp7797 gremp7797   {
   sort fips
   egen ovmean`var' = mean(`var')
   egen fm`var' = mean(`var'), by(fips)
   gen dmf`var' = `var' - fm`var' + ovmean`var'
   egen sm`var' = mean(`var'), by(sic3)
   gen dmfs`var' = `var' - fm`var' - sm`var' + 2*ovmean`var'
   drop ovmean`var' fm`var' sm`var'
}



foreach var in  gremp7797  {
   sort fips
   egen ovmean`var' = mean(`var')
   egen fm`var' = mean(`var'), by(fips)
   gen dmf`var' = `var' - fm`var' + ovmean`var'
   egen sm`var' = mean(`var'), by(sic3)
   gen dmfs`var' = `var' - fm`var' - sm`var' + 2*ovmean`var'
   drop ovmean`var' fm`var' sm`var'
}



    keep if univsur==1
   collapse (mean) dmfpmfg1980 dmfpuse1980 dmfzpmfg1980 dmfzpuse1980 dmfzcuse1980 dmfdemp7797 dmfgremp7797  dmfspmfg1980 dmfspuse1980 dmfszpmfg1980 dmfszpuse1980  dmfsdemp7797 dmfsgremp7797, by(sic3 numsic3)
   merge 1:1 sic3 using ${inter}sic3_names_list.dta, update
   drop if _m==2
   drop _m
      gen sic2 = substr(sic3,1,2)
   merge m:1 sic2 using ${inter}sic2_names.dta, update
   drop if _m==2
   drop _m
   replace name_sic3 = sic2_name if name_sic3=="" | substr(name_sic3,1,18)=="[Undocumented Code"  | substr(name_sic3,1,18)=="[Undocumented code"


replace sectorname = "AgForFish" if substr(sic3,1,1)=="0" & sectorname==""
replace sectorname = "MiningConstruc" if substr(sic3,1,1)=="1" & sectorname==""
replace sectorname = "MfgFoodChem" if substr(sic3,1,1)=="2" & sectorname==""
replace sectorname = "MfgHeavyElec" if substr(sic3,1,1)=="3" & sectorname==""
replace sectorname = "TranspComm" if substr(sic3,1,1)=="4" & sectorname==""
replace sectorname = "TradeWholeRetail" if substr(sic3,1,1)=="5" & sectorname==""
replace sectorname = "FIRE" if substr(sic3,1,1)=="6" & sectorname==""
replace sectorname = "ServicesRepair" if substr(sic3,1,1)=="7" & sectorname==""
replace sectorname = "ServicesProf" if substr(sic3,1,1)=="8" & sectorname==""
replace sectorname = "PubAdmin" if substr(sic3,1,1)=="9" & sectorname==""

   gen excludegr = .
   sum dmfgremp7797, det
   replace excludegr=1 if (dmfgremp7797<r(p1) | dmfgremp7797>=r(p99))
   sum dmfzcuse1980, det
   replace excludegr = 1 if dmfzcuse1980<r(p1) |  dmfzcuse1980>=r(p99)
   reg dmfgremp7797 dmfzcuse1980 if excludegr!=1
   matrix b = e(b)
   local r2: display%5.4f e(r2)
   local coeff: display %5.2f b[1,1]

*************
*** Fig 4 ***
*************

* sic-3 name labels
   graph tw (scatter dmfgremp7797 dmfzcuse1980 if excludegr!=1, mlabel(name_sic3) mlabposition(0) msymbol(i)) (lfit dmfgrv1emp7797 dmfzcuse1980 if excludegr!=1),   xtitle("Innovation Index") ytitle("1977-1997 Emp Growth") leg(off) graphregion(color(white)) bgcolor(white) saving(${graphs}gremp7797_zcuse1980_sic3label_v3.gph, replace) 
   
* sic-3 number labels
   graph tw (scatter dmfgremp7797 dmfzcuse1980 if excludegr!=1, mlabel(sic3) mlabposition(0) msymbol(i)) (lfit dmfgrv1emp7797 dmfzcuse1980 if excludegr!=1),   xtitle("Innovation Index") ytitle("1977-1997 Emp Growth") leg(off) graphregion(color(white)) bgcolor(white) saving(${graphs}gremp7797_zcuse1980_sic3_v3.gph, replace) 


*****************
*** END Fig 4 ***
*****************
